package cn.gson.crm.model.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import com.mysql.jdbc.StringUtils;
import cn.gson.crm.model.entity.Dept;

public class DeptDao {

	private Integer pageSize = 3;

	/**
	 * 通过部门id查询部门
	 * 
	 * @param status
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public Dept getDeptById(int status, String id) throws SQLException {
		String sql = "select * from dept where status= ? and id = ?";
		Map<String, Object> data = Jdbc.getInstace().executeQuery(sql, status, id);
		Dept dept = null;
		if (data != null) {
			dept = new Dept();
			dept = this.buildDept(data);
		}
		return dept;
	}

	/**
	 * 通过名字查询部门
	 * 
	 * @param deptName
	 * @return
	 * @throws SQLException
	 */
	public Dept getDeptByName(String deptName) throws SQLException {
		String sql = "select * from dept where dept_name = ?";
		Map<String, Object> data = Jdbc.getInstace().executeQuery(sql, deptName);
		Dept dept = null;
		if (data != null) {
			dept = new Dept();
			dept = this.buildDept(data);
		}
		return dept;
	}

	/**
	 * 通过上级部门id查询上级部门名称
	 * 
	 * @param parentId
	 * @return
	 * @throws SQLException
	 */
	private String getParentDeptName(int parentId) throws SQLException {
		String sql = "select dept_name from dept where id = ? ";
		Map<String, Object> data = Jdbc.getInstace().executeQuery(sql, parentId);
		String parentDeptName = null;
		if (data != null) {
			parentDeptName = data.get("dept_name").toString();
		}
		return parentDeptName;
	}

	/**
	 * 创建部门
	 * 
	 * @param dept
	 * @return
	 * @throws SQLException
	 */
	public Boolean createDept(Dept dept) throws SQLException {
		String sql = "insert into dept(dept_name,dept_manager,parent_id)values(?,?,?);";
		return Jdbc.getInstace().executeUpdate(sql, dept.getDeptName(), dept.getDeptManager(), dept.getParentId());
	}
	


	/**
	 * 修改部门
	 * 
	 * @param dept
	 * @return
	 * @throws SQLException
	 */
	public Boolean changeDept(Dept dept) throws SQLException {
		String sql = "UPDATE  dept set dept_name = ? ,dept_manager =?,parent_id = ? where id =? ;";
		System.out.println(dept.getDeptName() + dept.getDeptManager() + dept.getParentId() + dept.getId());
		boolean judge = Jdbc.getInstace().executeUpdate(sql, dept.getDeptName(), dept.getDeptManager(),
				dept.getParentId(), dept.getId());
		System.out.println("更改" + judge);
		return judge;
	}
	
	/**
	 * 修改部门状态
	 * 在网页上体现删除或创建
	 * @param dept
	 * @param status
	 * @return
	 * @throws SQLException
	 */
	public Boolean changeDept(Dept dept,Integer status) throws SQLException {
		String sql = "UPDATE  dept set dept_name = ? ,dept_manager =?,parent_id = ?,status=? where id =? ;";
		boolean judge = Jdbc.getInstace().executeUpdate(sql, dept.getDeptName().trim(), dept.getDeptManager().trim(),
				dept.getParentId(),status,dept.getId());
		return judge;
	}
	
	/**
	 * 获得部门列表
	 * 
	 * @param status
	 * @return
	 * @throws SQLException
	 */
	public List<Dept> getDeptList(int status) throws SQLException {
		String sql = "select * from dept where status = ? order by id desc";
		List<Map<String, Object>> dataList = Jdbc.getInstace().executeQueryForList(sql, status);
		return buildDept(dataList);

	}

	/**
	 * 有条件的分页查询
	 * 
	 * @param DeptName
	 * @param deptManager
	 * @param status
	 * @param page
	 * @return
	 * @throws SQLException
	 */
	public List<Dept> getDeptList(String deptName, String deptManager, int status, int page) throws SQLException {
		int start = (page - 1) * pageSize;
		List<Object> params = new ArrayList<>();
		String sql = "select * from dept where status=? ";
		sql = buildParamsAnd(sql, params, deptName, deptManager, status);
		sql += "order by id desc limit ?,?";
		params.add(start);
		params.add(pageSize);
		return queryDept(sql, params.toArray());

	}

	/**
	 * 获取部门的子部门
	 * 
	 * @param status
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public List<Dept> getSonDept(int status, String id) throws SQLException {
		String sql = "select * from dept where status = ? and parent_id = ? order by id desc";
		List<Map<String, Object>> dataList = Jdbc.getInstace().executeQueryForList(sql, status, id);
		return buildDept(dataList);
	}

	/**
	 * 获取 部门的子部门 有条件的分页查询
	 * 
	 * @param status
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public List<Dept> getSonDept(int page, String deptName, String deptManager, int status, String id)
			throws SQLException {
		int start = (page - 1) * pageSize;
		String sql = "select * from dept where status = ? and parent_id = ?";
		List<Object> params = new ArrayList<>();
		sql = buildParamsAnd(sql, params, deptName, deptManager, status, id);
		sql += " order by id desc limit ?,?";
		System.out.println(sql);
		params.add(start);
		params.add(pageSize);
		return queryDept(sql, params.toArray());
	}

	/**
	 * 获取不包括自己的部门
	 * 
	 * @param status
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public List<Dept> getDeptListNotContainsSelf(int status, String id) throws SQLException {
		String sql = "select * from dept where status = ? and id != ? order by id desc";
		List<Map<String, Object>> dataList = Jdbc.getInstace().executeQueryForList(sql, status, id);
		return buildDept(dataList);
	}
	
	/**
	 * 获取除了自己和子部门列表
	 * @param status
	 * @param id
	 * @return
	 * @throws SQLException 
	 */
	public List<Dept> getDeptListNotContainsSelfAndSon(int status, String id) throws SQLException{
		//获取所有部门
		List<Dept> allDeptList = getDeptList(1);
		for (int i = 0; i < allDeptList.size(); i++) {
			if(allDeptList.get(i).getDeptName().equals(getDeptById(status, id).getDeptName())){
				allDeptList.remove(i);
			}
		}
		if(allDeptList!=null){
				//获取子部门
				List<Dept> sonDeptList = getSonDept(status, id);
				//去除子部门
				if(sonDeptList !=null){
					for (int i = 0; i < allDeptList.size(); i++) {
						for (int j = 0; j < sonDeptList.size();j++) {
							if(allDeptList.get(i).getDeptName().equals(sonDeptList.get(j).getDeptName())){
								allDeptList.remove(i);
							}
						}
					}
			}
		}
		return allDeptList;
	}

	/**
	 * 通过id删除部门
	 * 
	 * @param status
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public Boolean deletDepartment(int status, String id) throws SQLException {
		String sql = "update  dept set status =?  where id = ? ";
		boolean result = Jdbc.getInstace().executeUpdate(sql, status, id);
		return result;

	}

	/**
	 * 获取子部门总页数
	 * 
	 * @param status
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public Integer getSonDeptTotalPage(int status, String id) throws SQLException {
		String sql = "select count(*) total from dept where status = ? and parent_id = ?";
		Map<String, Object> data = Jdbc.getInstace().executeQuery(sql, status, id);
		Integer total = Integer.parseInt((String) data.get("total"));
		return (int) Math.ceil(total.doubleValue() / pageSize);
	}

	/**
	 * 获取子部门总页数 查询时子部门的总页数
	 * 
	 * @param status
	 * @param id
	 * @param DeptName
	 * @param deptManager
	 * @return
	 * @throws SQLException
	 */
	public Integer getSonDeptTotalPage(int status, String id, String deptName, String deptManager) throws SQLException {
		String sql = "select count(*) total from dept where status = ? and parent_id = ?";
		List<Object> params = new ArrayList<>();
		sql=buildParamsAnd(sql, params, deptName, deptManager, status, id);
		Map<String, Object> data = Jdbc.getInstace().executeQuery(sql, params.toArray());
		Integer total = 0 ;
		if(data.get("total")!=null){
			total =  Integer.parseInt(data.get("total").toString());
		}
		return (int) Math.ceil(total.doubleValue() / pageSize);
	}

	/**
	 * 获取总页数
	 * 
	 * @param status
	 * @return
	 * @throws SQLException
	 */
	public Integer getTotalPage(int status) throws SQLException {
		String sql = "select count(*) total  from dept where status = ? ";
		Map<String, Object> data = Jdbc.getInstace().executeQuery(sql, status);
		Integer total = 0 ;
		if(data.get("total")!=null){
			total =  Integer.parseInt(data.get("total").toString());
		}
		return (int) Math.ceil(total.doubleValue() / pageSize);

	}

	/**
	 * 获取有条件查询的总页数
	 * 
	 * @param status
	 * @param DeptName
	 * @param deptManager
	 * @return
	 * @throws SQLException
	 */
	public Integer getTotalPage(int status, String deptName, String deptManager) throws SQLException {
		String sql = "select count(*) total  from dept where status = ? ";
		List<Object> params = new ArrayList<>();
		sql = buildParamsAnd(sql, params, deptName, deptManager, status);
		Map<String, Object> data = Jdbc.getInstace().executeQuery(sql, params.toArray());
		Integer total = 0 ;
		if(data.get("total")!=null){
			total =  Integer.parseInt(data.get("total").toString());
		}
		return (int) Math.ceil(total.doubleValue() / pageSize);

	}

	/**
	 * 查询部门
	 * 
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	private List<Dept> queryDept(String sql, Object... params) throws SQLException {
		List<Map<String, Object>> dataList = Jdbc.getInstace().executeQueryForList(sql, params);
		return buildDept(dataList);
	}

	/**
	 * 组装部门列表
	 * 
	 * @param dataList
	 * @return
	 * @throws SQLException
	 */
	private List<Dept> buildDept(List<Map<String, Object>> dataList) throws SQLException {
		List<Dept> rows = null;
		if (dataList != null && dataList.size() > 0) {
			rows = new ArrayList<>();
			for (Map<String, Object> data : dataList) {
				rows.add(buildDept(data));
			}
		}
		return rows;
	}

	/**
	 * 构建部门
	 * 
	 * @param data
	 * @return
	 * @throws SQLException
	 */
	private Dept buildDept(Map<String, Object> data) throws SQLException {
		Dept dept = new Dept();
		dept.setId(Integer.parseInt(data.get("id").toString()));
		dept.setDeptName(data.get("dept_name").toString());
		dept.setDeptManager((String) data.get("dept_manager"));
		dept.setCreateData((Date) data.get("create_time"));
		dept.setStatus(Integer.parseInt(data.get("status").toString()));
		if (data.get("parent_id") != null) {
			dept.setParentId(Integer.parseInt(data.get("parent_id").toString()));
			// 获取上级部门名称
			dept.setParentDeptName(getParentDeptName(Integer.parseInt(data.get("parent_id").toString())));
		}
		return dept;
	}

	/**
	 * 构建参数和sql
	 * 
	 * @param sql
	 * @param params
	 * @param DeptName
	 * @param deptManager
	 * @param status
	 * @return
	 */
	private String buildParamsAnd(String sql, List<Object> params, String deptName, String deptManager, int status) {
		params.add(status);
		if (!StringUtils.isNullOrEmpty(deptName)) {
			sql += "and dept_name like ? ";
			params.add("%" + deptName.trim() + "%");
			System.out.println(sql);
		}
		if (!StringUtils.isNullOrEmpty(deptManager)) {
			sql += "and dept_Manager like ? ";
			params.add("%" + deptManager.trim() + "%");
			System.out.println(sql);
		}
		return sql;
	}

	private String buildParamsAnd(String sql, List<Object> params, String deptName, String deptManager, int status,
			String id) {
		params.add(status);
		params.add(id);
		if (!StringUtils.isNullOrEmpty(deptName)) {
			sql += " and dept_name like ? ";
			params.add("%" + deptName.trim() + "%");
			System.out.println(sql);
		}
		if (!StringUtils.isNullOrEmpty(deptManager)) {
			sql += " and dept_Manager like ? ";
			params.add("%" + deptManager.trim() + "%");
			System.out.println(sql);
		}
		return sql;
	}
}
